# 导入模块，创建mysql数据的连接，创建游标
# 执行sql命令，创建员工表（编号、姓名、性别、生日、工资）
# 插入若干条数据
# 查询出数据
# 修改部分数据
# 删除数据
# 提交事务、关闭游标、关闭连接
import pymysql
conn = pymysql.connect(
    host='192.168.88.100',
    port=3306,
    user='root',
    password='123456',
    db='tedu',
    charset='utf8'
)
cur = conn.cursor()
"""
create_sql = '''
    create table emp(
        eid int,
        ename varchar(16),
        gender varchar(1),
        birthday date,
        salary decimal(7, 2)
    );
'''
cur.execute(create_sql)

# 插入数据
insert_sql = 'insert into emp values(%s, %s, %s, %s, %s)'
cur.executemany(insert_sql, [
    (1, '小明', '男', '1999-12-12', 8000),
    (2, '小红', '女', '2002-06-18', 5000),
    (3, '小东', '男', '2005-11-01', 3400),
    (4, '小军', '男', '2001-07-03', 6000),
    (5, '小丽', '女', '2003-02-28', 4000)
])

# 查询数据
cur.execute('select * from emp')
print(cur.fetchone())
print(cur.fetchmany(2))
print(cur.fetchall())

# 修改数据
update_sql = 'update emp set salary=%s where eid=%s'
cur.executemany(update_sql, [(5500, 2), (3900, 3)])
"""
# 删除数据
delete_sql = 'delete from emp where eid=%s'
cur.executemany(delete_sql, [(2, ), (4, )])

conn.commit()
cur.close()
conn.close()

